Statistics collection using path-identifiers for relational databases

ABSTRACT

Disclosed are a system, method, and computer readable medium for collecting statistics associated with data in a database. The method comprises determining an amount of memory needed to collect statistics for data associated with a defined data type in a relational database. The defined data type is based upon a mark-up language using a tree structure with one or more root-to-node paths therein. The amount of memory as determined is allocated for collecting the statistics for the data of the defined data type. A statistics collection is performed for the data of the defined data type in a single pass through the database and within the amount of memory which has been allocated.

CROSS-REFERENCE TO RELATED APPLICATIONS

The present patent application is a continuation of U.S. patentapplication Ser. No. 11/435,017 now U.S. Pat. No. 7,613,682, which wasfiled on May 16, 2006 and commonly assigned herewith to InternationalBusiness Machines, which is hereby incorporated by reference in itsentirety. Further this application is related to the inventor'sapplication “STATISTICS COLLECTION USING PATH-VALUE PAIRS FOR RELATIONALDATABASES ”, Ser. No. 11/435,353, now 7,472,108, which was filed on May16, 2006 and commonly assigned herewith to International BusinessMachines Corporation. This related application is incorporated herein byreference in its entirety.

FIELD OF THE INVENTION

The present invention generally relates to the field of databases, andmore particularly relates to collecting statistics associated with datain a database.

BACKGROUND OF THE INVENTION

Mark-up languages such as XML have become popular for managing data thatis stored in databases. Therefore, relational databases have evolvedinto hybrid systems that store both relational data and XML data. Infact, in more recent versions of International Business Machine's DB/2Database, XML was introduced as a data type. Moreover, SQL/XML andXQuery are new query languages on he XML data type. Optimization ofsearch queries for the XML data type stored in database often relies onusing statistics. These statistics help bound searches and queries.

However, one problem with optimizing queries using the XML data type isdetermining which statistics to collect. Another problem with optimizingqueries using the XML data type is how to collect statistics in abounded memory space. Still another problem with optimizing queries withthe XML data type is estimating the cardinality of paths withindatabase.

Therefore a need exists to overcome the problems with the prior art asdiscussed above.

SUMMARY OF THE INVENTION

Briefly, in accordance with the present invention, disclosed are asystem, method, and computer readable medium for collecting statisticsassociated with data in a database. The method comprises determining anamount of memory needed to collect statistics for data associated with adefined data type in a relational database. The defined data type isbased upon a mark-up language using a tree structure with one or moreroot-to-node paths therein. The amount of memory as determined isallocated for collecting the statistics for the data of the defined datatype. A statistics collection is performed for the data of the defineddata type in a single pass through the database and within the amount ofmemory which has been allocated.

In another embodiment of the present invention, a system for collectingstatistics associated with data in a database is disclosed. The systemcomprises at least one information processing system communicativelycoupled to a database. The information processing system includes amemory estimator for determining an amount of memory needed to collectstatistics for data associated with a defined data type in the database.The defined data type is based upon a mark-up language using a treestructure with one or more root-to-node paths therein. The informationprocessing system also includes a memory allocator for allocating theamount of memory as determined for collecting the statistics for thedata of the defined data type. A statistics collector performs acollection of statistics for the data of the defined data type in asingle pass through the database and within the amount of memory whichhas been allocated.

In yet another embodiment of the present invention a computer readablemedium for collecting statistics associated with data in a database isdisclosed. The computer readable medium comprises instructions fordetermining an amount of memory needed to collect statistics for dataassociated with a defined data type in a relational database. Thedefined data type is based upon a mark-up language using a treestructure with one or more root-to-node paths therein. The amount ofmemory as determined is allocated for collecting the statistics for thedata of the defined data type. A statistics collection is performed forthe data of the defined data type in a single pass through the databaseand within the amount of memory which has been allocated.

One advantage of the present invention is that statistics for a datatype represented in a mark-up language such as XML can be collected moreefficiently in a relational database. Another advantage is that thestatistics collecting procedure is performed within a given amount ofmemory. Yet another advantage is that the statistics for the data typeare collected within a single pass of a database table.

BRIEF DESCRIPTION OF THE DRAWINGS

The accompanying figures where like reference numerals refer toidentical or functionally similar elements throughout the separateviews, and which together with the detailed description below areincorporated in and form part of the specification, serve to furtherillustrate various embodiments and to explain various principles andadvantages all in accordance with the present invention.

FIG. 1 is a block diagram illustrating an exemplary system, according toan embodiment of the present invention;

FIG. 2 is a block diagram illustrating a more detailed view of aninformation processing system, according to an embodiment of the presentinvention;

FIG. 3 is a logic flow diagram for optimizing queries execution plans,according to an embodiment of the present invention;

FIG. 4 is a graphical representation of root-to-node paths for an XMLdocument, according to an embodiment of the present invention;

FIG. 5 is an operational flow diagram illustrating an overall exemplaryprocess of collecting statistics for a database, according to thepresent invention;

FIG. 6 is an operational flow diagram illustrating an exemplary processof collecting XML statistics, according to an embodiment of the presentinvention;

FIG. 7 is an operational flow diagram illustrating a more detailedprocess of collecting XML statistics, according to an embodiment of thepresent invention; and

FIG. 8 is an operational flow diagram illustrating another exemplaryprocess of collecting XML statistics, according to an embodiment of thepresent invention.

DETAILED DESCRIPTION

The present invention as would be known to one of ordinary skill in theart could be produced in hardware or software, or in a combination ofhardware and software. However in one embodiment the invention isimplemented in software. The system, or method, according to theinventive principles as disclosed in connection with the preferredembodiment, may be produced in a single computer system having separateelements or means for performing the individual functions or stepsdescribed or claimed or one or more elements or means combining theperformance of any of the functions or steps disclosed or claimed, ormay be arranged in a distributed computer system, interconnected by anysuitable means as would be known by one of ordinary skill in the art.

According to the inventive principles as disclosed in connection withthe preferred embodiment, the invention and the inventive principles arenot limited to any particular kind of computer system but may be usedwith any general purpose computer, as would be known to one of ordinaryskill in the art, arranged to perform the functions described and themethod steps described. The operations of such a computer, as describedabove, may be according to a computer program contained on a medium foruse in the operation or control of the computer, as would be known toone of ordinary skill in the art. The computer medium, which may be usedto hold or contain the computer program product, may be a fixture of thecomputer such as an embedded memory or may be on a transportable mediumsuch as a disk, as would be known to one of ordinary skill in the art.

The invention is not limited to any particular computer program or logicor language, or instruction but may be practiced with any such suitableprogram, logic or language, or instructions as would be known to one ofordinary skill in the art. Without limiting the principles of thedisclosed invention any such computing system can include, inter alia,at least a computer readable medium allowing a computer to read data,instructions, messages or message packets, and other computer readableinformation from the computer readable medium. The computer readablemedium may include non-volatile memory, such as ROM, Flash memory,floppy disk, Disk drive memory, CD-ROM, and other permanent storage.Additionally, a computer readable medium may include, for example,volatile storage such as RAM, buffers, cache memory, and networkcircuits.

Furthermore, the computer readable medium may include computer readableinformation in a transitory state medium such as a network link and/or anetwork interface, including a wired network or a wireless network thatallows a computer to read such computer readable information. Thepresent invention, according to an embodiment, overcomes problems withthe prior art by providing a more efficient mechanism for memory copyoperations. The present invention allows the processor to continueexecuting subsequent instructions during a memory copy operation therebyavoiding unnecessary processor downtime.

Exemplary System

According to an embodiment of the present invention, as shown in FIG. 1,an exemplary system 100 for collecting statistics associated with datain a database is shown. In one embodiment, the system 100 includes acentral storage system 102, a network 104, and one or more informationprocessing systems 106, 108, 110. The network 104, in one embodiment, isa wide area network, local area network, wireless network, and the like.

The central storage system 102 includes one or more databases 112, 114.In one embodiment, the databases 112, 114 are hybrid databases withrelational capabilities and comprehensive native support for mark-uplanguages such as extensible mark-up language (“XML”). It should benoted that although the present discussion is directed towards XML othermark-up language can apply. The central storage system 102 also includesa query optimizer 116 for optimizing queries received from theinformation processing systems 106, 108, 110. In one embodiment, thequery optimizer 116 is a cost-based optimizer that enumerates searchspace by generating feasible query execution plans, collects statistics,and estimates cardinality and cost of the plans using the statistics.The query optimizer 116, based on the estimated costs, chooses the bestquery execution plan.

The query optimizer 116, in one embodiment, includes a memory estimator118 and statistics collector 120. The memory estimator 118 estimates theamount of memory needed for collecting XML statistics and is discussedin greater detail below. The statistics collector 120 collectsstatistics related to data in the database. The statistics collector120, in one embodiment, can collect statistics for both relational dataand XML data. The statistics collector 120 is discussed in greaterdetail below.

Although shown as residing in the query optimizer, the memory estimator118, and the statistics collector 120, in one embodiment, residesoutside of the query optimizer 116 within the central storage system102. In another embodiment, the memory estimator 118 and/or thestatistics collector 120, in one embodiment, reside on one or moreremote systems such as the information processing systems 106, 108, 110.Additionally, the functions of the memory estimator 118 and thestatistics collector 120, in one embodiment, can be performed by asingle component.

Exemplary Central Storage System

FIG. 2 is a block diagram illustrating a more detailed view of thecentral storage system 102, according to the present invention. Thecentral storage system 102 is based upon a suitably configuredprocessing system adapted to implement the exemplary embodiment of thepresent invention. Any suitably configured processing system issimilarly able to be used as the information processing system 102 byembodiments of the present invention, for example, a personal computer,workstation, or the like. The central storage system 102 includes acomputer 202. The computer 202 has a processor 204 that is connected toa main memory 206, mass storage interface 208, terminal interface 210,and network adapter hardware 212. A system bus 214 interconnects thesesystem components. The mass storage interface 208 is used to connectmass storage devices, such as data storage device 216, to the centralstorage system 102 system. One specific type of data storage device is acomputer readable medium such as a floppy disk drive, which may be usedto store data to and read data from a CD 218 or a floppy diskette (notshown). Another type of data storage device is a data storage deviceconfigured to support, for example, NTFS type file system operations.

The main memory 206 comprises databases 112, 114, which in oneembodiment, comprise both relational data and XML data. The main memory206 also includes the query optimizer 116. As discussed above, the queryoptimizer 116 includes the memory estimator 118. Prior to statisticscollector 120 collecting statistics for XML data, the memory estimator118 estimates the amount of memory needed for performing the XMLstatistics collection. For example, for each XML column in a databasetable, the memory estimator 118 estimates the amount of memory for thedata members required for an XML statistics collecting routine. Thememory estimator 118 also determines the amount of memory required for anumber of reservoirs and the work areas of each reservoir. Reservoirs,for example, are data members of an XML statistics collecting routinethat are used in collecting specific XML statistics. The memoryestimator 118 also determines the amount of memory needed for anyfilters that are to be used in the statistics collecting process. Thenumber of distinct path-identifiers that reach a value are also used inestimating the amount of required memory. It should be noted that thisis not an exhaustive list of what is used by the memory estimator 118for estimating the total amount of memory for the XML statisticscollecting process. Once the memory estimator 118 determines the memoryneeded to collect the XML statistics, the required memory is allocatedto the statistic collecting procedure. The XML statistics collector 122collects the statistics within the allocated memory.

Bounding the memory for the statistics collecting procedure isadvantageous because the statistics are collected while the database112, 114 is live. If the required amount of memory is not determinedbefore the statistics collecting procedure starts, the procedure can runout of memory during run time and need to abort. By estimating theamount of required memory beforehand, the memory estimator 118 candetermine if the required memory is available. If the required amount ofmemory is not available, the user, in one embodiment, is prompted toallocate more memory for the statistics collecting procedure.

The statistics collector 120 collects database table statistics througha traditional table scan operation. One method for collecting statisticsis described in Patricia G. Selinger, Morton M. Astrahan, Donald D.Chamberlin, Raymond A. Lorie, Thomas G. Price: Access Path Selection ina Relational Database Management System. SIGMOD Conference 1979: 23-34,which is hereby incorporated by reference in its entirety. At each rowof the database table, each column in the row is processed. If a columnis of XML type, the statistics collector 120 performs an XML statisticscollection procedure. Each XML document is traversed so that therelevant XML statistics can be collected. In one embodiment, pathdistribution statistics and path-value distribution statistics arecollected.

For each XML column in the database table, the statistics collector 120collects frequency distribution of all distinct root-to-node paths thatappear in a given XML column. Each root-to-node path, in one embodiment,is associated with a path-identifier. Some root-to-node paths may occurmore frequently than other paths. Therefore, for the top k1 (givennumber of) most frequently occurring path-identifiers (i.e. root-to-nodepaths) the statistics collector 120 counts, for each frequent path, thenumber of documents (i.e. rows) that include the path-identifier. Thestatistics collector 120 also counts, for each of the most frequentpath-identifiers, how many times the corresponding root-to-node pathappears in the XML column, e.g. node count. For example, if fivedocuments exist in an XML column and a path /a/b appears in only two ofthe documents, doc1 and doc2, a document count for the path /a/b is two.If the path /a/b appears twice in document doc1 and three times indocument doc2, the node count for the path is five.

Because an XML column is logically a collection that includes “similar”XML documents in most instances, the number of distinct path-identifiersin a given a column is usually small. As a result, as long as theparameter k1 is not too small, the top-k1 pairs (e.g. path-identifier,document count and path-identifier, node count) usually cover alldistinct path-identifiers in the column. If the number of distinctpath-identifiers in a column is large, only the exact counts for thepopular path-identifiers are stored.

In one embodiment, the statistics collector 120 collects a “catch-all”bucket for all of the path-identifiers in the given XML column. For the“catch-all” bucket the statistics collector 122 counts the number ofunique path-identifiers in the current column. One method of estimatingthe number of unique paths is described in M. Astrahan, M. Schkolnick,and K.-Y. Whang. Approximating the number of unique values of anattribute without sorting. Information Systems, 12(1):11-15, 1987. 147,which is hereby incorporated by reference in its entirety. Thestatistics collector 120 also counts the total number of nodes in thecurrent column. A document count sum is also calculated by thestatistics collector 120. For example, the number of documents that eachunique path occurs in is counted and these numbers are added together toget a document count sum.

As described above, the statistics collector 120 also collectspath-value distribution statistics. The statistics collector 120, forthe top k2 (given number of) most frequently occurring path-identifiersassociated with a value (e.g. text, number, and the like) counts foreach frequent path-identifier the number of documents (i.e. rows) thatinclude the path-identifier. The statistics collector 120 also counts,for each of the most frequent path-identifiers, how many times thecorresponding root-to-node path/value pair appears in the XML column,e.g. node count.

In one embodiment, the statistics collector 120 also collects a“catch-all” bucket for each path-identifier associated with a path thatreaches a value. For the “catch-all” bucket, the statistics collector120 counts the number of distinct values for each unique path thatreaches a value in the current column. One method for estimating thenumber of distinct values is described in M. Astrahan, M. Schkolnick,and K.-Y. Whang. Approximating the number of unique values of anattribute without sorting. Information Systems, 12(1):11-15, 1987. 147,which is hereby incorporated by reference in its entirety. For example,if a path a/b/ exists in the current column and has the followingoccurrences /a/b=3, /a/b=4, /a/b=5, /a/b=7 the number of distinct valuesis 4. The second highest and lowest value associated with the currentpath is also determined by the statistics collector 120. Also, for eachvalue associated with the current path, the statistics collector 120counts the number of times that each value occurs in the column. Thisallows the statistics collector 120 to determine a value count sum. Thenumber of documents that each value occurs is also counted by thestatistics collector to determine a document count sum.

In one embodiment, the statistics collector 120 uses sampling to collectthe above mentioned statistics for XML data types. Sampling improves theperformance of the statistics collection. The statistics collector, inone embodiment, uses two-stage sampling method. For example,document-level and reservoir sampling methods are used. Fordocument-level sampling, a user can select row-level/page-level samplingon XML columns. If a user chooses either one of these sampling levels afirst level sampling result is yielded, which includes a set of sampledXML documents. If the user chooses not to run document-level sampling,reservoir sampling is performed on all of the XML documents.

Reservoir sampling, in one embodiment, is used to obtain fourreservoirs, which are sampling arrays used to store sampled entries.Reservoir sampling is more fully described in Jeffrey Scott Vitter:Random Sampling with a Reservoir. ACM Trans. Math. Softw. 11(1): 37-57(1985), which is hereby incorporated by reference in its entirety. Aseach XML document is traversed by the statistics collector 120, eachpath-identifier is sent to a first reservoir, e.g. a reservoir forpath-identifiers at the node level (“Rpn”). A second reservoir, e.g. areservoir for path-identifiers at the document level (“Rpd”) receivesonly unique path-identifiers. For example, the Rpd reservoir does notreceive duplicate path-identifiers. This is accomplished, in oneembodiment, by using a filter such as a bloom filter. Bloom filters aremore fully describe in Burton Bloom, Space/time trade-offs in hashcoding with allowable errors, CACM, 13(7):422-426, July 1970, which ishereby incorporated by reference in its entirety.

Only the path-identifiers that pass the bloom filter are added to theRpd. Similarly, each path-identifier that is associated with a givenvalue is passed to a third reservoir, e.g. a reservoir for apath-identifier-value at the node level (“Rpvn”). Uniquepath-identifiers that are associated with a given value are added to afourth reservoir, e.g. a reservoir for a path-identifier-value at thedocument level (“Rpvd”). A second bloom filter is used to ensure that aduplicate path-identifier associated with a value is not passed to theRpvd. In one embodiment, the bloom filters are reset upon startingtraversal of a new document. It should be noted that only two bloomfilters are needed for the entire database table.

Each reservoir is summarized and extrapolated to obtain specific counts.For example, by summarizing the Rpn, a given number of the mostfrequently occurring path-identifiers can be identified according to anode count. By summarizing the Rpd, a given number of the mostfrequently occurring path-identifiers can be identified according to adocument count. Summarizing the Rpvn yields a given number of the mostfrequently occurring path-identifiers associated with a given valueaccording to a node count. By summarizing the Rpvd a given number of themost frequently occurring path-identifiers associated with a given valuecan be identified according to a document count. The number of distinctpath-identifiers for the “catch-all” bucket associated with thepath-identifiers and the number of the distinct values for the“catch-all” bucket associated with the path-identifiers reaching a valuecan also be determined from the reservoirs. When the statisticscollector 120 finishes collecting and summarizing the statistics, asystem catalogue is updated with the statistics. The query optimizer 116uses these statistics when determining access paths to the data. In oneembodiment, similar actions are performed to estimate a node count and adocument of path-identifiers associated with a value in a given valuerange.

Although illustrated as concurrently resident in the main memory 206, itis clear that respective components of the main memory 206 are notrequired to be completely resident in the main memory 206 at all timesor even at the same time. In one embodiment, the central storage system102 utilizes conventional virtual addressing mechanisms to allowprograms to behave as if they have access to a large, single storageentity, referred to herein as a computer system memory, instead ofaccess to multiple, smaller storage entities such as the main memory 206and data storage device 216. Note that the term “computer system memory”is used herein to generically refer to the entire virtual memory of thecentral storage system 102.

Although only one CPU 204 is illustrated for computer 202, computersystems with multiple CPUs can be used equally effectively. Embodimentsof the present invention further incorporate interfaces that eachincludes separate, fully programmed microprocessors that are used tooff-load processing from the CPU 204. Terminal interface 210 is used todirectly connect one or more terminals 220 to computer 202 to provide auser interface to the computer 202. These terminals 220, which are ableto be non-intelligent or fully programmable workstations, are used toallow system administrators and users to communicate with the centralstorage g system 102. The terminal 220 is also able to consist of userinterface and peripheral devices that are connected to computer 202 andcontrolled by terminal interface hardware included in the terminal I/F210 that includes video adapters and interfaces for keyboards, pointingdevices, and the like.

An operating system (not shown) included in the main memory is asuitable multitasking operating system such as the Linux, UNIX, WindowsXP, and Windows Server 2001 operating system. Embodiments of the presentinvention are able to use any other suitable operating system. Someembodiments of the present invention utilize architectures, such as anobject oriented framework mechanism, that allows instructions of thecomponents of operating system (not shown) to be executed on anyprocessor located within the central storage 102. The network adapterhardware 212 is used to provide an interface to the network 104.Embodiments of the present invention are able to be adapted to work withany data communications connections including present day analog and/ordigital techniques or via a future networking mechanism.

Although the exemplary embodiments of the present invention aredescribed in the context of a fully functional computer system, thoseskilled in the art will appreciate that embodiments are capable of beingdistributed as a program product via CD or DVD, e.g. CD 218, CD ROM, orother form of recordable media, or via any type of electronictransmission mechanism.

Exemplary Logic Flow Diagram

FIG. 3 illustrates a logic flow diagram 300 for generating an optimalquery execution plan, based at least in part on statistics collectedabout data in the database 112, 114. A query Q 302 is transmitted by aninformation processing system 106, 108, 110 and is received at thedatabase 112, 114. A query plan enumerator 304 generates a set of queryexecution plans 306 for executing the query. A selectivity estimator308, using the statistics discussed above, estimates the selectivity orcardinality of the data in the database 112, 114 and passes thisestimation to the query optimizer 116. The query optimizer 116 using theestimated cardinality, which was determined from the collectedstatistics, selects the optimal query execution plan 314. In oneembodiment, the query optimizer 116 is a cost-based optimizer andtherefore chooses an optimal query execution plan based on cost. Theoptimal query execution plan 314 is passed to a query execution engine316, which yields a query result 318.

Exemplary Root-To-Node Paths

FIG. 4 illustrates exemplary root-to-node paths for different XMLdocuments. FIG. 4 shows a partial XML column 402 for a database table.The partial XML column 402 comprises rows 404 with an XML document and adocument ID 406. The path trees 408, 410, 412 represent the structure ofXML data in the respective document. A path from the root of the XMLdocument is represented by each node 414 in the path 408, 410, 412. Aroot element of the document is represented by a root node 416.

As discussed above, the statistics collector 120 processes each documentin a column to collect the relevant statistics. For example, if theparameter k1 is three, the statistics collector 120 determines a pathnode count, path document count, path-value node count and pathvalue-document count for the three most frequently occurring paths.Using the path trees 408, 410, 412 in FIG. 4 as an example, the threemost frequently occurring paths are /A/B, /A, and /A/C/E. The path nodecount for the path /A/B is 4 because this path occurs four times in thecolumn. The path node count for the path /A is 3 and the path node countfor /A/C/E is also 3. The path document count for the path /A/B is 3because the path occurs in three documents. The path document counts forthe paths /A and /A/C/E is also 3.

A path-value node count, as discussed above, is the number of times aparticular path associated with a given values occurs in a column. Forexample, the path-value node count for the path A/C/E=33 is 2 becausethis path and value occur two times in the column. The path-valuedocument count is the number of documents that a path and its associatedvalue occur in. For example, the path-vale document count for the path/A/C/E=33 is 2 because this path and value occur in two documents. Ascan be seen, the statistics collector 120 collects exact values for thegiven number of most frequently occurring paths. However, as discussedabove, a “catch-all” bucket is collected for all of the path-identifiers(e.g. paths). The statistics collector 120 collects the number ofdistinct paths, sum of the node counts, and sum of the document counts.For example, the number of distinct paths /A, /A/B, /A/C, A/C/D, /A/C/E,/A/C/@G, /A/C/F is 7 in the example of FIG. 4. The sum of the nodecounts (A, A, A, B, B, B, B, C, C, C, D, E, E, E, F, F, @G) is 17.

The sum of the document counts is 15. For example, /A occurs in 3documents, /A/B occurs in 3 documents, /A/C occurs in 3 documents,/A/C/D occurs in 1 document, /A/C/E occurs in 3 documents, /A/C/@Goccurs in 1 document, and /A/C/F occurs in 1 document. The statisticscollector 120 also collects a “catch-all” bucket for each path thatreaches a value. For example, for the path /A/B, the statistics collects120 counts the number of distinct values (3, 4, 5, 7), which is 4. Thesecond highest value, i.e. 5, and the second lowest value, i.e. 4, arealso determined. The sum of the value counts, as discussed above, forthe path /A/B is 4. For example, the value 3 occurs one time, the value4 occurs one time, the value 5 occurs one time, and the value 7 occursone time. The sum of the document count, as discussed above, for thepath /A/B is 4. For example, the value 3 occurs in one document, thevalue 4 occurs in one document, the value 5 occurs in one document andthe value 7 occurs in one document. These calculations of repeated bythe statistics collector for the path /A/C/D, /A/C/E, /A/C/F, and/A/C/@G.

Exemplary Process For Collecting statistics

FIG. 5 shows an over process for collecting statistics for data in adatabase 112, 114. The operational flow diagram of FIG. 5 begins at step502 and flows directly to step 504. The memory estimator 118, at step504, determines the amount of memory required to collect XML statistics.For example, the memory estimator analyzes the number of distinct pathsthat reach a value to determine the amount of memory needed forpath-value “catch-all” buckets. This is only one example of what thememory estimator uses in calculating the total amount of memory requiredto collect XML statistics. The memory estimator 118, at sep 506,determines if the required amount of memory is available. If the resultof this determination is negative, a user is prompted, at step 508, toeither allocate more memory or abort the collection process. The controlflow then exits at step 510.

If the result of this determination is positive, the required memory, atstep 512, is allocated to the statistics collecting process. The XMLstatistics, at step 514, are collected within the allocated amountmemory and within a single pass of the database table. The control flowthen exits at step 516. One advantage of the present invention is thatthe memory required for the XML statistics collection is determinedbefore the process begins. This prevents the process from running out ofmemory during its operation. Also, because the database 112, 114 is liveduring the collection process, bounding the memory allows for efficientuse of the database 112, 114. Collecting the statistics in a single passallows the statistics to be collected quickly and efficiently.

An Exemplary Process of Collecting XML Statistics

FIG. 6 shows an exemplary process of collecting XML statistics in adatabase 112, 114. The operational flow diagram of FIG. 6 begins at step602 and flows directly to step 604. The statistics collector 120, atstep 604, scans each table in a database 112, 114. The statisticscollector 120, at step 606, determines if a row of a table has beenreached. If the result of this determination is negative, the statisticscollector 120 continues to monitor for a row.

If the result of this determination is positive, the statisticscollector 120, at step 608, processes each column in the row. Thestatistics collector 120, at step 610, determines if the current columnis of XML type. If the result of this determination if negative, normalstatistics collecting operations, at step 612, are performed. Thecontrol flow then exits at step 614. If the result of this determinationis positive, XML statistics collecting operations, at step 616, areperformed. The statistics collector 120, at step 618, collects pathdistribution statistics and path-value distribution statistics for thecurrent column. The control flow then exits, at step 620.

A More Detailed Process of Collecting XML Statistics

FIG. 7 shows a more detailed process of collecting XML statistics fordata in a database 112, 114. FIG. 7 shows an exemplary process forcollecting path distribution statistics. The operational flow diagram ofFIG. 7 begins at step 702 and flows directly to step 704. The statisticscollector 120, at step 704, determines a number of most frequentlyoccurring paths for each column. The statistics collector 120, at step706, counts the number of times that each frequent path occurs in thecolumn to obtain a node count associated with each frequent path in thegiven number of frequent paths. For each frequent path, the statisticscollector 120, at step 708, counts the number of documents that includethe path to obtain a document count.

The number of unique paths, at step 710, is also counted by thestatistics collector 120. The statistics collector 120, at step 712,counts for each unique path the number of documents that include thepath to obtain a document sum count. The number of nodes in the column,at step 714, is also counted by the statistics collector 120. Thecontrol flow then exits at step 716. In one embodiment, unique paths aredetermined by using a bloom filter. As each path-identifier associatedwith a path is passed through a bloom filter, duplicatepath-identifiers/path are prevented from being counted as a unique path.The statistics collected by the statistics collector 120, in oneembodiment, are used by the query optimizer 116 for selecting an optimalquery execution plan. For example, an optimal query execution plan canbe one that minimizes the cost of executing a specific query.

Another More Detailed Process of Collecting XML Statistics

FIG. 8 shows a more detailed process of collecting XML statistics fordata in a database 112, 114. FIG. 8 shows an exemplary process forcollecting path-value distribution statistics. The operational flowdiagram of FIG. 8 begins at step 802 and flows directly to step 804. Thestatistics collector 120, at step 804, determines a number of mostfrequently occurring paths that are each associated with a given value.For each frequent path with a value, the statistics collector 120, atstep 806, counts the number of times that the frequent path with thevalue occurs in the current column to obtain a path-value node count.The statistics collector 120, at step 808, also counts the number ofdocuments that include the frequent path with the value to obtain apath-value document count.

For each unique path, the statistics collector 120, at step 810, countsthe number of unique values associated with the path. The second highestand lowest value for each unique path, at step 812, is also counted bythe statistics collector 120. The statistics collector 120, at step 814,also counts for each unique path the number of times that each valueassociated with the unique path occurs to obtain a node sum count. Foreach unique path, the statistics collector 120, at step 816, also countsthe number of documents that each value of the path occurs to obtain adocument sum count. The control flow then exits at step 818. In oneembodiment, unique paths associated with a value are determined by usinga bloom filter. As each path-identifier associated with a path-valuepair is passed through a bloom filter, duplicate path-identifiersassociated with a path-value pair are prevented from being counted as aunique path-value pair.

Non-Limiting Examples

The present invention can be realized in hardware, software, or acombination of hardware and software. A system according to a preferredembodiment of the present invention can be realized in a centralizedfashion in one computer system or in a distributed fashion wheredifferent elements are spread across several interconnected computersystems. Any kind of computer system—or other apparatus adapted forcarrying out the methods described herein—is suited. A typicalcombination of hardware and software could be a general purpose computersystem with a computer program that, when being loaded and executed,controls the computer system such that it carries out the methodsdescribed herein.

In general, the routines executed to implement the embodiments of thepresent invention, whether implemented as part of an operating system ora specific application, component, program, module, object or sequenceof instructions may be referred to herein as a “program.” The computerprogram typically is comprised of a multitude of instructions that willbe translated by the native computer into a machine-readable format andhence executable instructions. Also, programs are comprised of variablesand data structures that either reside locally to the program or arefound in memory or on storage devices. In addition, various programsdescribed herein may be identified based upon the application for whichthey are implemented in a specific embodiment of the invention. However,it should be appreciated that any particular program nomenclature thatfollows is used merely for convenience, and thus the invention shouldnot be limited to use solely in any specific application identifiedand/or implied by such nomenclature.

Although specific embodiments of the invention have been disclosed,those having ordinary skill in the art will understand that changes canbe made to the specific embodiments without departing from the spiritand scope of the invention. The scope of the invention is not to berestricted, therefore, to the specific embodiments, and it is intendedthat the appended claims cover any and all such applications,modifications, and embodiments within the scope of the presentinvention.

1. A method, on an information processing system, for collectingstatistics associated with data in a database, the method comprising:determining an amount of memory needed to collect statistics for dataassociated with a defined data type in a relational database, whereinthe defined data type is based upon a mark-up language using a treestructure with one or more root-to-node paths therein; allocating theamount of memory as determined for collecting the statistics for thedata of the defined data type; and performing, with a processor, acollection of statistics for the data of the defined data type in asingle pass through the database and within the amount of memory whichhas been allocated.
 2. The method of claim 1, wherein the performing thecollection of statistics, further comprises: counting a total number ofinstances of at least one path-identifier within a given set ofdocuments; and determining at least one of a node count and a documentcount for the path-identifier based on the counting.
 3. The method ofclaim 2, wherein the at least one path-identifier is one of a givennumber of most frequently occurring path-identifiers in the given set ofdocuments.
 4. The method of claim 1, wherein the performing thecollection of statistics further comprises: counting, for each documentwithin a set of given documents, a total number of path-identifiers thatare each associated with a unique path, thereby resulting in a totalnumber of path-identifiers in the set of given documents that areassociated with the unique path; counting a total number of nodes withinthe set of given documents; and counting, for each path-identifier thatis associated with a unique path, a total number of documents in the setof given documents that include the path-identifier, thereby resultingin a sum of document counts.
 5. The method of claim 1, wherein theperforming the collection of statistics, further comprises performing atleast one of: sampling at a document level, wherein the samplingcomprises at least one of a row level sampling and a page levelsampling; and sampling using at least one reservoir.
 6. The method ofclaim 5, wherein the sampling using reservoirs comprises: traversingthrough at least one of sampled documents in a given set of documents;and all documents in the set of given set of documents for identifyingat least path-identifiers; passing data associated with eachpath-identifier that has been identified to a first reservoir; andpassing data associated with each path-identifier that has beenidentified and is unique within a particular document in the given setof documents to a second reservoir.
 7. The method of claim 6, furthercomprising: applying a first bloom filter to each path-identifier priorto passing the path-identifier to the second reservoir, therebypreventing a duplicate path-identifier within the particular documentfrom being passed to the second reservoir.
 8. The method of claim 7,further comprising: summarizing the first reservoir for identifying anode count for determining a given number of most frequently occurringpath-identifiers; and summarizing the second reservoir for identifying adocument count for determining the given number of most frequentlyoccurring path-identifiers.
 9. A system for collecting statisticsassociated with data in a database, the system comprising: at least oneinformation processing system communicatively coupled to a database, theinformation processing system comprising: a memory; a processor; amemory estimator, communicatively coupled to the memory and theprocessor, for determining an amount of memory needed to collectstatistics for data associated with a defined data type in the database,wherein the amount of memory also includes at least one of: an amount ofmemory needed for a set of reservoirs used to collect specificstatistics for the data associated with the defined data type and a workarea associated with each of the reservoirs, and an amount of memoryneeded for a set of filters to be used in collecting statistics for dataassociated with the defined data type, wherein the defined data type isbased upon a mark-up language using a tree structure with one or moreroot-to-node paths therein; a memory allocator, communicatively coupledto the memory and the processor, for allocating the amount of memory asdetermined for collecting the statistics for the data of the defineddata type; and a statistics collector, communicatively coupled to thememory and the processor, for performing a collection of statistics forthe data of the defined data type in a single pass through the databaseand within the amount of memory which has been allocated.
 10. The systemof claim 9, wherein the statistics collector determines a total numberof instances of at least one path-identifier within a given set ofdocuments in the database and wherein the statistics collectordetermines at least one of a node count and a document count for thepath-identifier based on the total number of instances, wherein the atleast one path-identifier is one of a given number of most frequentlyoccurring path-identifiers in the given set of documents.
 11. The systemof claim 9, wherein the statistics collector counts, for each documentwithin a set of given documents, a total number of path-identifiers thatare each associated with a unique path, thereby resulting in a totalnumber of path-identifiers in the set of given documents that areassociated with the unique path; counts a total number of nodes withinthe set of given documents; and counts for each path-identifier that isassociated with a unique path, a total number of documents in the set ofgiven documents that include the path-identifier, thereby resulting in asum of document counts.
 12. The system of claim 9, wherein theinformation system further comprises at least one sampling reservoir,wherein the statistics collector: traverses through at least one ofsampled documents in a given set of documents; and all documents in theset of given set of documents for identifying at least path-identifiers;passes data associated with each path-identifier that has beenidentified to a first reservoir; and passes data associated with eachpath-identifier that has been identified and is unique within aparticular document in the given set of documents to a second reservoir,wherein the first reservoir is summarized for identifying a node countfor determining a given number of most frequently occurringpath-identifiers, and wherein the second reservoir is summarized foridentifying a document count for determining the given number of mostfrequently occurring path-identifiers.
 13. The system of claim 12,wherein the information processing system further comprises at least onebloom filter that is applied to each path-identifier prior to passingthe path-identifier to the second reservoir, thereby preventing aduplicate path-identifier within the particular document from beingpassed to the second reservoir.
 14. A computer readable medium forcollecting statistics associated with data in a database, the computerreadable medium comprising instructions for: determining an amount ofmemory needed to collect statistics for data associated with a defineddata type in a relational database, wherein the defined data type isbased upon a mark-up language using a tree structure with one or moreroot-to-node paths therein; allocating the amount of memory asdetermined for collecting the statistics for the data of the defineddata type; and performing a collection of statistics for the data of thedefined data type in a single pass through the database and within theamount of memory which has been allocated.
 15. The computer readablemedium of claim 14, wherein the instructions for performing thecollection of statistics, further comprise instructions for: counting atotal number of instances of at least one path-identifier within a givenset of documents; and determining at least one of a node count and adocument count for the path-identifier based on the counting.
 16. Thecomputer readable medium of claim 15, wherein the at least onepath-identifier is one of a given number of most frequently occurringpath-identifiers in the given set of documents.
 17. The computerreadable medium of claim 15, wherein the instructions for performing thecollection of statistics further comprise instructions for: counting,for each document within a set of given documents, a total number ofpath-identifiers that are each associated with a unique path, therebyresulting in a total number of path-identifiers in the set of givendocuments that are associated with the unique path; counting a totalnumber of nodes within the set of given documents; and counting, foreach path-identifier that is associated with a unique path, a totalnumber of documents in the set of given documents that include thepath-identifier, thereby resulting in a sum of document counts.
 18. Thecomputer readable medium of claim 15, wherein the instructions forperforming the collection of statistics further comprise instructionsfor: sampling at a document level, wherein the sampling comprises atleast one of a row level sampling and a page level sampling; andsampling using at least one reservoir.
 19. The computer readable mediumof claim 18, wherein the instructions for sampling using reservoirsfurther comprise instructions for: traversing through at least one ofsampled documents in a given set of documents; and all documents in theset of given set of documents for identifying at least path-identifiers;passing data associated with each path-identifier that has beenidentified to a first reservoir; and passing data associated with eachpath-identifier that has been identified and is unique within aparticular document in the given set of documents to a second reservoir;and applying a first bloom filter to each path-identifier prior topassing the path-identifier to the second reservoir, thereby preventinga duplicate path-identifier within the particular document from beingpassed to the second reservoir.
 20. The computer readable medium ofclaim 19, further comprising instructions for: summarizing the firstreservoir for identifying a node count for determining a given number ofmost frequently occurring path-identifiers; and summarizing the secondreservoir for identifying a document count for determining the givennumber of most frequently occurring path-identifiers.